# -*- coding: utf-8 -*-
import MySQLdb
import MySQLdb.cursors
from xlwt import *
import xlrd
from xlutils.copy import copy
import os
import sys

reload(sys)
sys.setdefaultencoding('utf8')

"""
author：zhangqing
function:itjuzi标签 和 cv企业标签合并 验证
逻辑:1、读取excel文件获取标签清洗数据规则以 key\value 形式放入字典；
     2、分批获取存储it桔子企业的数据表 获取企业 和 企业在it桔子所对应的标签信息；
     3、验证开发打标签结果：根据cvid（it桔子企业 与 cvs企业对应的标识）获取cvs企业（开发清洗最终的结果表）最终打的标签结果，与excel中规则做对比
     4、将对比结果与规则不一致有问题的数据写入excel供查找问题原因
"""

def read_excel():
    # 打开文件（CV&IT.xlsx  文件就是CV&IT桔子标签规则文件的exxcel，只是我重新命名了去掉了中文）
    workbook = xlrd.open_workbook(r'G:\CV&IT.xlsx')
    # 获取所有sheet
    print workbook.sheet_names() # [u'sheet1', u'sheet2']
    sheet2_name = workbook.sheet_names()[1]

    # 根据sheet索引或者名称获取sheet内容
    sheet2 = workbook.sheet_by_index(1) # sheet索引从0开始
    sheet2 = workbook.sheet_by_name('2-标签洗数据规则')

    # sheet的名称，行数，列数
    print sheet2.name,sheet2.nrows,sheet2.ncols
    nrows = sheet2.nrows    # 获取行总数
    ncols = sheet2.ncols    #获取列总数

    mapDict = {}
    #循环打印每一行的内容
    for i in range(1,nrows):
        print sheet2.row_values(i)
        rowsValue = sheet2.row_values(i)
        for j in range(2,ncols):
            closValue = sheet2.cell_value(i,j)
            if closValue != "":
                if j>=2:
                    closValue = sheet2.cell_value(i,j)

                #因为excel规则中，有可能一个key对应两个值，如’房产SaaS‘标签既对应’房产’又对应‘SAAS’，此时会出现同一个key对应多个value，所以字典方案为 使用set作为dict的值 值不允许重复
                mapDict.setdefault(closValue,set()).add(sheet2.cell_value(i,0))
                    # mapDict[closValue] = sheet2.cell_value(i,0)

    searchItjuzi(mapDict)


def searchItjuzi(mapDict):
    db = MySQLdb.connect("rm-2ze9cx91355z4mk9hpo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6",
                         "test", charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()

    sql = "SELECT COUNT(a.id) a FROM spider_itjuzi_cvid a "
    cursor.execute(sql)
    resultsCount = cursor.fetchone()
    pageCount = resultsCount["a"]/50
    # for index in range(pageCount):
    for index in range(1994,2141):
    #上次跑到50000
        limitStart = index * 50
        print("limitStart====================================================",limitStart)

        #查询itjuzi中企业和对应标签信息
        itjuziSql = "SELECT cv_id,enterprise,tag,'' AS beizhu,'' AS excelValue,'' AS tianyanchaName " \
                    "FROM spider_itjuzi_cvid ORDER BY cv_id DESC limit %d,50 "%limitStart

        # itjuziSql = "SELECT cv_id,enterprise,tag,'' AS beizhu,'' AS excelValue,'' AS tianyanchaName " \
        #             "FROM spider_itjuzi_cvid WHERE cv_id = '39601980'"
        cursor.execute(itjuziSql)
        itjuziResults1 = cursor.fetchall()
        fields = cursor.description
        #根据cvid获取清洗后结果做对比
        compareItjuziTags(itjuziResults1,mapDict,fields)

    db.close()

def compareItjuziTags(itjuziResults1,mapDict,fields):
    db = MySQLdb.connect("rm-2zenl5bol3h6o6hqllo.mysql.rds.aliyuncs.com", "cvs2.0_read_only", "aaKcKA6iDXyJVeF6", "cv_data_info", charset='utf8',
                         cursorclass=MySQLdb.cursors.DictCursor)
    cursor = db.cursor()
    for itjuziIndex in range(len(itjuziResults1)):
        itjuziCvId = itjuziResults1[itjuziIndex].get(u"cv_id")
        itjuziTag = itjuziResults1[itjuziIndex].get(u"tag")
        itjuziTagStr = itjuziTag.split(",")
        for index in range(len(itjuziTagStr)):
            itjuziTag = itjuziTagStr[index]
            #itjuzi中的tag拆分循环去excel中规则找对应结果，找到后看dc_company_tag清洗结果表中此企业是否打对了标签
            if itjuziTag in mapDict.keys():
                excelValueList = list(mapDict[itjuziTag])
                for excelValue in excelValueList:
                    tianyanchaSql = "SELECT cv_id,company_name,tag_name FROM dc_company_tag WHERE `status`=1 AND cv_id ='%s' AND tag_name='%s'" %(itjuziCvId,excelValue)

                    cursor.execute(tianyanchaSql)
                    tianyanchaResults1 = cursor.fetchall()

                    if len(tianyanchaResults1)==0:
                           tianyanchaTagNameSql = "SELECT cv_id,GROUP_CONCAT(tag_name) AS tianyanchaName " \
                                                  "FROM dc_company_tag WHERE `status`=1 AND cv_id = '%s' GROUP BY cv_id " %(itjuziCvId)

                           cursor.execute(tianyanchaTagNameSql)
                           tianyanchaTagNameResults1 = cursor.fetchall()
                           if len(tianyanchaTagNameResults1) == 0:
                               itjuziResults1[itjuziIndex].update(beizhu=u'天眼查dc_company_tag表没有此条企业标签信息')
                               itjuziResults1[itjuziIndex].update(excelValue=excelValue)
                               itjuziResults1[itjuziIndex].update(tianyanchaName='')
                               writeExcel(itjuziResults1[itjuziIndex],fields)
                           else:
                               tianyanchaName = tianyanchaTagNameResults1[0].get(u"tianyanchaName")
                               itjuziResults1[itjuziIndex].update(beizhu=u'洗后的企业标签与对应的规则值不完全匹配')
                               itjuziResults1[itjuziIndex].update(excelValue=excelValue)
                               itjuziResults1[itjuziIndex].update(tianyanchaName=tianyanchaName)
                               writeExcel(itjuziResults1[itjuziIndex],fields)
            else:
                itjuziResults1[itjuziIndex].update(beizhu=u'此标签在规则内没有：'+itjuziTag)
                itjuziResults1[itjuziIndex].update(excelValue='')
                itjuziResults1[itjuziIndex].update(tianyanchaName='')
                writeExcel(itjuziResults1[itjuziIndex],fields)

    db.close()

def writeExcel(results,fields):

    fileName = 'dataTagResult.xlsx'
    p1 = os.path.exists(fileName)

    if p1:
        f = xlrd.open_workbook(fileName, formatting_info=True)
        sheet = f.sheet_by_index(0)
        rowNum = sheet.nrows
        newbook =copy(f)
        newsheet = newbook.get_sheet(0)

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print rowNum,j,x
            newsheet.write(rowNum,j,x)

        newbook.save(fileName)

    else:

        workbook  = Workbook(encoding = 'utf-8')
        table = workbook .add_sheet('data')

        for field in range(0,len(fields)):
            table.write(0,field,fields[field][0])

        num =0
        for j,q in enumerate(results):
            x = results["%s"%fields[num][0]]
            num = num+1
            print 1,j,x
            table.write(1,j,x)

        workbook .save(fileName)
